﻿<%@ WebHandler Language="C#" Class="ApsWorkStationHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
using System.IO;
using NPOI.SS.UserModel;
using DatabaseAccess.DBUtility;
/// <summary>
/// add by longb 2022-08-11
/// </summary>

public class ApsWorkStationHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    ResourceMaintBusiness resourceBusiness = new ResourceMaintBusiness();//设备类
    WorkShiftBusiness workShiftBusiness = new WorkShiftBusiness();//班次类
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();//通用类
    TestRunBillBusiness runBill = new TestRunBillBusiness();
    WorkStationBusiness wsBill = new WorkStationBusiness();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "GetFactory": //查询所属分厂
                    GetFactory(context);
                    break;
                case "GetShift"://查询班
                    GetShift(context);
                    break;
                case "GetEmployee"://查询员工
                    GetEmployee(context);
                    break;
                case "GetWorkStation"://日历关联到班----查询
                    GetWorkStation(context);
                    break;
                case "SaveWorkStation"://日历关联到班----保存、更新
                    SaveWorkStation(context);
                    break;
                case "DeleteWorkStation"://删除工位
                    DeleteWorkStation(context);
                    break;    
                case "GetWorkStationEmployee"://查询--工位关联的人员信息
                    GetWorkStationEmployee(context);
                    break;
                case "ImportWorkStation"://工位信息导入
                    ImportWorkStation(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }


    #region MyRegion

    /// <summary>
    /// 初始化页面数据
    /// </summary>
    /// <param name="context"></param>
    public void GetFactory(HttpContext context)
    {
        DataSet dtSet = new DataSet();
        //获取工厂
        Dictionary<string, string> dic = new Dictionary<string, string>();
        //dic.Add("FactoryGone", "");
        DataTable dt = commonBusiness.GetFactory(dic);
        DataView dv = dt.DefaultView;
        dv.RowFilter = " Factoryid='" + m_UserInfo.FactoryID.ToString() + "'";
        DataTable dtFactory = dv.ToTable();
        dtFactory.TableName = "dtFactory";
        dtSet.Tables.Add(dtFactory);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { DBDataSet = dtSet }));
    }
    /// <summary>
    /// 查询班组
    /// </summary>
    /// <param name="context"></param>
    public void GetShift(HttpContext context)
    {
        string factoryid = context.Request["factoryid"].ToString();
        if (string.IsNullOrWhiteSpace(factoryid))
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "没有组数据" }));
            return;
        }
        DataTable dtTeam = wsBill.GetShiftByFacyoryId(factoryid);
        DataRow drteam = dtTeam.NewRow();
        drteam["SHIFTID"] = "";
        drteam["SHIFTNAME"] = "请选择";
        dtTeam.Rows.InsertAt(drteam, 0);//插入到第一行
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取组成功", DBTable = dtTeam }));
    }
    /// <summary>
    /// 查询分厂下人员
    /// </summary>
    public void GetEmployee(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var ids = context.Request["ids"].ToString();
        var para = new Dictionary<string, string>();
        para.Add("FactoryID", m_UserInfo.FactoryID.ToString());
        para.Add("FullName", dt.Rows[0]["FullName"].ToString());
        para.Add("EmployeeName", dt.Rows[0]["EmployeeName"].ToString());
        uMESProcessResult result = new uMESProcessResult();
        var edtt = runBill.GetEmployees(para);
        DataView Aspec = edtt.DefaultView;
        Aspec.RowFilter = "EMPLOYEEID not IN('"+ids.Replace(",","','")+"')";
        DataTable dtll = Aspec.ToTable();
        result.DBTable = dtll;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    ///  工位维护--查询
    /// </summary>
    /// <param name="context"></param>
    public void GetWorkStation(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        uMESProcessResult result = new uMESProcessResult();
        var para = new Dictionary<string, string>();
        para.Add("factoryId", dt.Rows[0]["factory"].ToString());
        para.Add("shiftId", dt.Rows[0]["shift"].ToString());
        result.DBTable = wsBill.GetWorkStation(para);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 工位维护--保存
    /// </summary>
    /// <param name="context"></param>
    public void SaveWorkStation(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        uMESProcessResult result = new uMESProcessResult();
        var para = new Dictionary<string, string>();
        var WorkStationID = string.IsNullOrWhiteSpace(dt.Rows[0]["WorkStationID"].ToString()) ? Guid.NewGuid().ToString() : dt.Rows[0]["WorkStationID"].ToString();
        para.Add("WorkStationID", WorkStationID);
        para.Add("WorkStationName", dt.Rows[0]["WorkStationName"].ToString());
        para.Add("ShiftID", dt.Rows[0]["ShiftID"].ToString());
        para.Add("FactoryID", dt.Rows[0]["FactoryID"].ToString());
        para.Add("QuotaMins", dt.Rows[0]["QuotaMins"].ToString());
        //para.Add("IsBottLeneck", dt.Rows[0]["IsBottLeneck"].ToString());
        para.Add("CreateEmployeeID", m_UserInfo.EmployeeID.ToString());
        DataTable dtt = JsonConvert.DeserializeObject<DataTable>(context.Request["employees"].ToString());
        var Check = wsBill.CheckExistsWorkStationName(para);//检验同一班是否已存在相同工位
        if (Check.Rows.Count>0)
        {
            para["WorkStationName"] = string.Empty;
        }
        var mainSql = string.IsNullOrWhiteSpace(dt.Rows[0]["WorkStationID"].ToString()) ? wsBill.InsertWorkStation(para) : wsBill.UpdateWorkStation(para);
        result.Result = wsBill.BindWorkStationEmployee(para, dtt, mainSql);
        result.Message = result.Result ? "保存成功" : "保存失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 删除工位
    /// </summary>
    /// <param name="context"></param>
    private void DeleteWorkStation(HttpContext context)
    {
        var result = new uMESProcessResult();
        var workStationid = context.Request["workStationid"].ToString();
        var selectSQL = @"SELECT SUM(N) N FROM (
                        SELECT COUNT(1) N FROM WORKFLOWSTEP w WHERE w.WORKSTATIONIDS LIKE '%{0}%'
                        UNION
                        SELECT COUNT(1) N FROM APSPLANDETAILPRE a WHERE a.WORKSTATIONID ='{0}'
                        UNION
                        SELECT COUNT(1) N FROM APSPLANDETAIL a2 WHERE a2.WORKSTATIONID ='{0}'
                        )";
        var dt = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, workStationid));
        if (dt.Rows[0]["N"].ToString().Equals("0")) {
            result.Result = DatabaseHelper.ExecuteSql(string.Format("delete from workstation where workStationid='{0}'", workStationid)) > 0;
            result.Message = result.Result?"删除成功":"删除失败";
        } else { result.Message = "工位已被使用，禁止删除"; }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    /// <summary>
    /// 工位/人员关系--查询
    /// </summary>
    /// <param name="context"></param>
    public void GetWorkStationEmployee(HttpContext context)
    {
        string WorkStationID = context.Request["WorkStationID"].ToString();
        uMESProcessResult result = new uMESProcessResult();
        result.DBTable = wsBill.GetWorkStationEmployee(WorkStationID);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="context"></param>
    public void ImportWorkStation(HttpContext context) 
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }

            if (!System.IO.Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = System.IO.Path.GetExtension(file.FileName);
            string fileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除
            NPOI.SS.UserModel.IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            DataTable dt = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            uLMS_CommonFunction.DeleteFile(fpath);
            uMESProcessResult result = SaveData(dt);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            uLMS_CommonFunction.DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 处理Excle单元格数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="IsXls"></param>
    /// <returns></returns>
    private uMESProcessResult SaveData(DataTable dt)
    {
        DataTable Errordt = new DataTable();//格式错误行数据集
        Errordt.Columns.Add("workstation");
        Errordt.Columns.Add("Error");
        DataTable mdt = new DataTable();//筛选主表无误数据集
        mdt.Columns.Add("mainSql");
        mdt.Columns.Add("workStationid");
        DataTable dedt = new DataTable();//细表人员数据
        dedt.Columns.Add("workStationid");
        dedt.Columns.Add("employeeid");
        DataTable dedtt = new DataTable();//细表人员数据1
        dedtt.Columns.Add("workStationid");
        dedtt.Columns.Add("employeeid");
        uMESProcessResult result = new uMESProcessResult();
        var heardStr = string.Empty;
        if(!dt.Columns.Contains("分厂")){
            heardStr+="【分厂】列，";
        }
        if(!dt.Columns.Contains("班")){
            heardStr+="【班】列，";
        }
         if(!dt.Columns.Contains("工位名")){
            heardStr+="【工位名】列，";
        }
         if(!dt.Columns.Contains("工位额定工时")){
            heardStr+="【工位额定工时】列，";
        }
         if(!dt.Columns.Contains("员工号")){
            heardStr+="【员工号】列，";
        } 
        if(!string.IsNullOrWhiteSpace(heardStr)){
            result.Message="Excle 中未包含"+heardStr.TrimEnd(',')+"数据";
            result.Result = false;
            return result;
        }
        var i = 2;
        foreach(DataRow row in dt.Rows)
        {
            var factoryID = GetFactoryIDbyName(row["分厂"].ToString());
            var errStr = string.Empty;
            if (string.IsNullOrWhiteSpace(factoryID)) {
                errStr += "【"+row["分厂"].ToString()+"】不存在,";
            }
            var shiftID = GetShiftByFactoryIDandShiftName(factoryID, row["班"].ToString());
            if (string.IsNullOrWhiteSpace(shiftID)) {
                errStr += "分厂对应班：【" + row["班"].ToString() + "】不存在,";
            }
            var dic=new Dictionary<string,string>();
            dic.Add("WorkStationName", row["工位名"].ToString());
            dic.Add("ShiftID", shiftID);
            dic.Add("FactoryID", factoryID);
            dic.Add("QuotaMins", row["工位额定工时"].ToString());
            dic.Add("CreateEmployeeID", m_UserInfo.EmployeeID.ToString());
            var WorkStationID =string.Empty ;
            var mainSql = string.Empty;
            var sdt = wsBill.CheckExistsWorkStationName(dic);
            if (sdt.Rows.Count > 0) {
                WorkStationID = sdt.Rows[0]["WorkStationID"].ToString();
                dic.Add("WorkStationID", WorkStationID);
                dic["WorkStationName"] = string.Empty;
                mainSql =  wsBill.UpdateWorkStation(dic);
            } else {
                WorkStationID=Guid.NewGuid().ToString();
                dic.Add("WorkStationID",WorkStationID);
                mainSql = wsBill.InsertWorkStation(dic);
            }
            var employeeNo = row["员工号"].ToString();
            var empStr = string.Empty;
            
            if (!string.IsNullOrWhiteSpace(employeeNo)) {
                foreach (var NO in employeeNo.Split(','))
                {
                    var employeeid = GetEmployeeIDbyNO(NO);
                    if (string.IsNullOrWhiteSpace(employeeid)) { empStr += NO + ","; } else {
                        DataRow deRow = dedtt.NewRow();
                        deRow["employeeid"] = employeeid;
                        deRow["workStationid"] = WorkStationID;
                        dedtt.Rows.Add(deRow);
                    }
                }
            }
            if (!string.IsNullOrWhiteSpace(errStr))
            {
                DataRow ERow = Errordt.NewRow();
                ERow["workstation"] = row["工位名"].ToString();
                ERow["Error"] = "第" + i + "行：" + errStr.TrimEnd(',') + (!string.IsNullOrWhiteSpace(empStr) ? "；员工号【" + empStr.TrimEnd(',') + "】不存在" : "");
                Errordt.Rows.Add(ERow);
                dedtt.Clear();
            }
            else {
                if (!string.IsNullOrWhiteSpace(empStr)) {
                    DataRow ERow = Errordt.NewRow();
                    ERow["workstation"] = row["工位名"].ToString();
                    ERow["Error"] = "第" + i + "行：员工号【" + empStr.TrimEnd(',')+"】不存在";
                    Errordt.Rows.Add(ERow);
                }
                dedt.Merge(dedtt);
                dedt.AcceptChanges();
                dedtt.Clear();
                DataRow mdtRow = mdt.NewRow();
                mdtRow["mainSql"] = mainSql;
                mdtRow["workStationid"] = WorkStationID;
                mdt.Rows.Add(mdtRow);
            }
            i++;
        }
        
        if (mdt.Rows.Count > 0)
        {
            result.Result = wsBill.ImportWorkStationEmployee(mdt,dedt);
            result.Message = result.Result ? "导入成功" + mdt.Rows.Count + "条数据" + (Errordt.Rows.Count > 0 ? ";导入失败" + Errordt.Rows.Count + "条数据" : "") : "导入失败";
        }
        result.ReturnData = Errordt;
        return result;
    }

    public string GetFactoryIDbyName(string factoryName) 
    {
        var SqlStr = @"SELECT f.FACTORYID,f.FACTORYNAME,f.DESCRIPTION FROM FACTORY f where f.FACTORYNAME='" + factoryName + "'";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(SqlStr);
        return dt.Rows.Count>0?dt.Rows[0]["FACTORYID"].ToString():"";
    }
    public string GetShiftByFactoryIDandShiftName(string factoryid,string shiftname) 
    {
        var SqlStr = @"SELECT s.SHIFTID,s.SHIFTNAME FROM SHIFT s WHERE s.FACTORYID ='" + factoryid + "' AND s.SHIFTNAME='" + shiftname + "'";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(SqlStr);
        return dt.Rows.Count > 0 ? dt.Rows[0]["SHIFTID"].ToString() : "";
    }
    public string GetEmployeeIDbyNO(string NO) 
    {
        var SqlStr = @"SELECT e.EMPLOYEENAME,e.EMPLOYEEID,e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEENAME ='"+NO+"'";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(SqlStr);
        return dt.Rows.Count > 0 ? dt.Rows[0]["EMPLOYEEID"].ToString() : "";
    }
    #endregion

}